﻿-- =============================================
-- Author:		Mauro Buccioli
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[SearchGuest] 
	-- Add the parameters for the stored procedure here
	@Surname varchar(256) = null, 
	@Name varchar(256) = null,
	@GenderId int = null,
	@Mail varchar(256) = null,
	@CF varchar(20) = null,
	@PI varchar(20) = null,
	@TotalCount int output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	SET @TotalCount = (SELECT COUNT(GuestId)
    FROM [SmartHotel].[dbo].[Guests]
	WHERE (@Surname IS NULL OR Surname LIKE @Surname + '%') AND
	(@Name IS NULL OR Name LIKE @Name + '%') AND
	(@GenderId IS NULL OR GenderId = @GenderId) AND
	(@Mail IS NULL OR Mail IS NULL OR Mail like @Mail + '%') AND
	(@CF IS NULL OR CF IS NULL OR CF like @CF + '%') AND
	(@PI IS NULL OR [PI] IS NULL OR [PI] like @PI + '%'))

    -- Insert statements for procedure here
	SELECT TOP 50 [GuestId]
      ,[Surname]
      ,[Name]
      ,[DateOfBirth]
      ,[CF]
      ,[PI]
      ,[ForeignCity]
      ,[Notes]
      ,[Mail]
      ,[BirthCountryId]
      ,[GenderId]
      ,[PhoneNumber]
      ,[CompanyId]
      ,[BirthCityId]
	FROM [SmartHotel].[dbo].[Guests]
	WHERE (@Surname IS NULL OR Surname LIKE @Surname + '%') AND
	(@Name IS NULL OR Name LIKE @Name + '%') AND
	(@GenderId IS NULL OR GenderId = @GenderId) AND
	(@Mail IS NULL OR Mail IS NULL OR Mail like @Mail + '%') AND
	(@CF IS NULL OR CF IS NULL OR CF like @CF + '%') AND
	(@PI IS NULL OR [PI] IS NULL OR [PI] like @PI + '%')

END